package com.zhkj.crawl.service.impl;

import com.zhkj.crawl.config.Config;
import com.zhkj.crawl.config.Constants;
import com.zhkj.crawl.config.StringPool;
import com.zhkj.crawl.entity.ResultAmz;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Service;

import java.util.HashMap;
import java.util.List;

@Service
@Slf4j
public class ResultAmzService {

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    /**
     * 批量插入
     * @param list
     * @return
     */
    public int batchInsert(List<ResultAmz> list){
        String sql = String.format("INSERT ignore INTO result_amz%s(`asin`,`site`,`month`,`rank_str`,`rating_str`,`star_str`,`rank1`,`rank2`,`rank3`,`rating`,`star`,`url`) " +
                "VALUES(:asin,:site,:month,:rankStr,:ratingStr,:starStr,:rank1,:rank2,:rank3,:rating,:star,:url)", Config.TB_CURRENT);
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list);

        return jdbcTemplate.batchUpdate(sql, beanSources).length;
    }

    public List<ResultAmz> getRetryList(int limit){
        int maxRetry = Config.MAX_RETRY_RESULT_AMZ;
        String month = Constants.getCurMon();
        String orderBy = "";
        if(Constants.isWindows){
            orderBy = "ORDER BY id DESC ";
        }
        String siteSql = StringPool.EMPTY;
        if(StringUtils.isNotBlank(Config.SITE_CURRENT)){
            String tmp = Config.SITE_CURRENT, val = StringPool.EMPTY;
            if(tmp.contains(StringPool.COMMA)){
                for (String s : tmp.split(StringPool.COMMA)) {
                    val += String.format("'%s',", s);
                }
                val = val.substring(0, val.length() - 1);
                siteSql = String.format("AND site in(%s)", val);
            }else {
                siteSql = String.format("AND site='%s'", tmp);
            }
        }
        String sql = String.format("SELECT * FROM result_amz%s WHERE rank_str IS NULL AND (rating_str IS NOT NULL AND star_str IS NOT NULL) AND month=%s %s AND times<%s %s LIMIT %s",
                Config.TB_CURRENT, month, siteSql, maxRetry, orderBy, limit);
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ResultAmz.class));
    }

    public List<ResultAmz> getDoneList(){
        String month = Constants.getCurMon();
        String sql = String.format("SELECT b.id,a.asin,a.site,a.rank_str,a.rank1,a.rank2,a.rank3,a.rating, a.star, a.url,a.update_time " +
                        "from result_amz%s a,listing%s b \n" +
                        "where a.site=b.site and a.asin=b.plat_id\n" +
                        "and a.month='%s' and a.is_pushed = 0",
                Config.TB_CURRENT, Config.TB_CURRENT, month);
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ResultAmz.class));
    }

    public int updatePushState(){
        String month = Constants.getCurMon();
        String sql = String.format("update result_amz%s SET is_pushed=1 where month='%s'",
                Config.TB_CURRENT, month);
        return jdbcTemplate.update(sql, new HashMap<>());
    }



    public int updateRetryList(List<ResultAmz> list){
        String sql = String.format("UPDATE result_amz%s SET rank_str = :rankStr, rank1 = :rank1, rank2 = :rank2, rank3 = :rank3, times = :times WHERE id = :id", Config.TB_CURRENT);
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list);

        return jdbcTemplate.batchUpdate(sql, beanSources).length;
    }

}